-- Funcion para tener el estado de credito de un idpad
-- Anda con el esquema de limite de credito probado en forma simple. No se si en todos los casos.
-- Ric - 2/2/2023


CREATE OR REPLACE FUNCTION limite_credito.get_limite_rapido(
	pidpad integer,
	sys_unisuc character varying,
	pcodpre character varying)
    RETURNS TABLE(limite numeric, saldo_ctacte numeric, saldo_valores numeric, saldo_stock numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 100
AS $BODY$
DECLARE
BEGIN

RETURN QUERY EXECUTE '
WITH GRUPOS 
AS (SELECT PAD.ID AS IDPAD, PADLIMGRU.COD, CASE WHEN PARTICULAR.IDPAD IS NULL THEN GRUPAL.LIMITE ELSE PARTICULAR.LIMITE END LIMITE
FROM PAD
LEFT JOIN PADLIM GRUPAL     ON PAD.TIPOPADA = GRUPAL.TIPOPADA AND (PAD.CODPADR = GRUPAL.CODPADR OR PAD.CODPADR ILIKE GRUPAL.CODPADR || ''.%'')
LEFT JOIN PADLIM PARTICULAR ON PAD.ID = PARTICULAR.IDPAD
LEFT JOIN PADLIMGRU 		ON GRUPAL.CODPADLIMGRU = PADLIMGRU.COD OR PARTICULAR.CODPADLIMGRU = PADLIMGRU.COD
)

SELECT CAST(MIN(LIMITE) AS NUMERIC) AS LIMITE, 
       CAST(COALESCE(SUM(Saldo_CtaCte),0) AS NUMERIC)  as Saldo_CtaCte, 
	   CAST(COALESCE(Sum(Saldo_Valores),0) AS NUMERIC) as Saldo_Valores, 
	   CAST(COALESCE(sum(Saldo_Stock),0) AS NUMERIC) as Saldo_Stock
FROM
(SELECT GRUPOS.IDPAD, GRUPOS.LIMITE, SUM(CTEM.IMPOMONEDA * SIGNO) AS Saldo_CtaCte, 0.00 as Saldo_valores, 0.00 as Saldo_Stock
FROM GRUPOS
LEFT JOIN PADLIMVAL ON PADLIMVAL.CODPADLIMGRU = GRUPOS.COD
INNER JOIN CTEM ON CTEM.TIPOVALT = PADLIMVAL.TIPOVALT AND CTEM.IDPAD = GRUPOS.IDPAD
INNER JOIN COMM ON COMM.ID = CTEM.IDCOMM
WHERE (CTEM.IMPOMONPEND > 0 OR (CTEM.IDVAL IS NOT NULL AND COMM.IDPAD = GRUPOS.IDPAD)) 
AND GRUPOS.IDPAD = ' || pIdPad || 
' AND  (INDEX(''' || SYS_UniSuc || ''', COMM.codambsuc) > 0 )
GROUP BY GRUPOS.IDPAD, GRUPOS.LIMITE
UNION
SELECT GRUPOS.IDPAD, GRUPOS.LIMITE, 0, SUM(CTEM.IMPO * SIGNO) AS Saldo_Valores, 0
FROM GRUPOS
LEFT JOIN PADLIMVAL ON PADLIMVAL.CODPADLIMGRU = GRUPOS.COD
INNER JOIN CTEM ON CTEM.TIPOVALT = PADLIMVAL.TIPOVALT
INNER JOIN COMM ON COMM.ID = CTEM.IDCOMM
INNER JOIN VAL  ON VAL.ID = CTEM.IDVAL
INNER JOIN PAD CAJA ON VAL.IDPADQUELOTIENE = CAJA.ID AND CAJA.CODPADR ILIKE ''F.CAJ.CAJ%''
WHERE (COMM.IDPAD = GRUPOS.IDPAD) 
AND GRUPOS.IDPAD = ' || pIdPad || 
' AND  (INDEX(''' || SYS_UniSuc || ''', COMM.codambsuc) > 0 )
GROUP BY GRUPOS.IDPAD, GRUPOS.LIMITE
UNION
SELECT GRUPOS.IDPAD, GRUPOS.LIMITE, 0, 0, SUM(PRE_OBTENERPRECIO(''' || pCodPre || ''',ARTM.IDART,NULL,NULL) * CANTPENDIENTE * 
CASE WHEN PADLIMEST.SIGNO = ''+'' THEN 1 ELSE -1 END) AS Saldo_Remitos
FROM GRUPOS
INNER JOIN PADLIMEST ON PADLIMEST.CODPADLIMGRU = GRUPOS.COD
INNER JOIN ARTM ON ARTM.CODSTKEST = PADLIMEST.CODSTKEST
INNER JOIN COMM ON COMM.ID = ARTM.IDCOMM AND COMM.IDPAD = GRUPOS.IDPAD
WHERE GRUPOS.IDPAD = ' || pIdPad || 
' AND  (INDEX(''' || SYS_UniSuc || ''', COMM.codambsuc) > 0 )
AND ARTM.CantPendiente > 0
GROUP BY GRUPOS.IDPAD, GRUPOS.LIMITE
UNION
SELECT GRUPOS.IDPAD, GRUPOS.LIMITE, 0, 0, 0
FROM GRUPOS
WHERE GRUPOS.IDPAD = ' || pIdPad || ') Pendientes
GROUP BY idpad';

END;
$BODY$;

ALTER FUNCTION limite_credito.get_limite_rapido(integer, character varying, character varying)
    OWNER TO abacouser;
    
--
SELECT aaversion('489.00','488.11');
--
